<html>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*,java.util.*"%>
				<%@ page import="javax.sql.rowset.CachedRowSet,com.sun.rowset.CachedRowSetImpl;" %>
				<%
					Connection conn = null;
					try {
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");

						String action = request.getParameter("action");
						// Check if an insertion is requested
						if (action != null && action.equals("insert")) {
							// Begin transaction
							conn.setAutoCommit(false);
							PreparedStatement pstmt = conn.prepareStatement(
					                "INSERT INTO course VALUES (?, ?, ?, ?, ?, ?)");
					        pstmt.setString(1, request.getParameter("course_number"));
					        pstmt.setString(2, request.getParameter("lab_required"));
					        pstmt.setString(3, request.getParameter("letter_grade_allowed"));
					        pstmt.setString(4, request.getParameter("su_allowed"));
					        pstmt.setInt(5, Integer.parseInt(request.getParameter("min_units")));
					        pstmt.setInt(6, Integer.parseInt(request.getParameter("max_units")));
					        pstmt.executeUpdate();
					        
					     	// INSERT prerequisite records
					        String[] prerequisites = request.getParameterValues("prerequisites");
					     	if (prerequisites != null) {
					       		for (String prerequisite : prerequisites) {
					        		pstmt = conn.prepareStatement(
					            		    "INSERT INTO prerequisite VALUES (?, ?)");
					        		pstmt.setString(1, request.getParameter("course_number"));
					        		pstmt.setString(2, prerequisite);
					        		pstmt.executeUpdate();
					        	}
					     	}
					        
							// Commit transaction
							conn.commit();
							conn.setAutoCommit(true);
						}
						
						// Check if an update is requested
						if (action != null && action.equals("update")) {
							// Begin transaction
	                        conn.setAutoCommit(false);
	                        
	                        // Create the prepared statement and use it to
	                        // UPDATE the course attributes in the course table.
	                        PreparedStatement pstmt = conn.prepareStatement(
	                            "UPDATE course SET lab_required = ?, letter_grade_allowed = ?, "+
	                            	"su_allowed = ?, min_units = ?, max_units = ? WHERE course_number = ?");

	                        pstmt.setString(1, request.getParameter("lab_required"));
				            pstmt.setString(2, request.getParameter("letter_grade_allowed"));
				            pstmt.setString(3, request.getParameter("su_allowed"));
				            pstmt.setInt(4, Integer.parseInt(request.getParameter("min_units")));
				            pstmt.setInt(5, Integer.parseInt(request.getParameter("max_units")));
				            pstmt.setString(6, request.getParameter("course_number"));
	                        pstmt.executeUpdate();
	                        
	                        // UPDATE prerequisites.
	                        pstmt = conn.prepareStatement("DELETE FROM prerequisite WHERE course_number = ?");
	                     	pstmt.setString(1, request.getParameter("course_number"));
	                     	pstmt.executeUpdate();

	                     	String[] prerequisites = request.getParameterValues("prerequisites");
					     	if (prerequisites != null) {
					       		for (String prerequisite : prerequisites) {
					        		pstmt = conn.prepareStatement(
					            		    "INSERT INTO prerequisite VALUES (?, ?)");
					        		pstmt.setString(1, request.getParameter("course_number"));
					        		pstmt.setString(2, prerequisite);
					        		pstmt.executeUpdate();
					        	}
					     	}
	                        
	                        // Commit transaction
	                        conn.commit();
	                        conn.setAutoCommit(true);
						}
						
						if (action != null && action.equals("delete")) {
							// Begin transaction
	                        conn.setAutoCommit(false);
	                        
	                        PreparedStatement pstmt = conn.prepareStatement(
			                        "SELECT class_id FROM class WHERE course_number = ?");
			                    pstmt.setString(1, request.getParameter("course_number"));
			                   	ResultSet rs = pstmt.executeQuery();
			                   	CachedRowSet cachedClassId = new CachedRowSetImpl();
			                   	cachedClassId.populate(rs);
			                   	
			                   	while(cachedClassId.next()){
			                   		int class_id = cachedClassId.getInt("class_id");
			                   		pstmt = conn.prepareStatement(
					                        "SELECT section_id FROM section WHERE class_id = ?");
				                    pstmt.setInt(1, class_id);
				                   	rs = pstmt.executeQuery();
				                   	CachedRowSet cachedSectionId = new CachedRowSetImpl();
				                   	cachedSectionId.populate(rs);
				                   	while(cachedSectionId.next()){
				                   		int section_id = cachedSectionId.getInt("section_id"); 
				                   		//delete meeting
				                      	pstmt = conn.prepareStatement(
				                            "DELETE FROM meeting WHERE section_id = ?");
				                      	pstmt.setInt(1, section_id);
				                      	pstmt.executeUpdate();
				                      	
				                      	//delete review session
				                      	pstmt = conn.prepareStatement(
				                            "DELETE FROM review_session WHERE section_id = ?");
				                      	pstmt.setInt(1, section_id);
				                      	pstmt.executeUpdate();
				                      	
				                      	//delete enroll
				                      	pstmt = conn.prepareStatement(
				                            "DELETE FROM enroll WHERE section_id = ?");
				                      	pstmt.setInt(1, section_id);
				                      	pstmt.executeUpdate();
				                      	
				                      	//delete section
				                      	pstmt = conn.prepareStatement(
				                            "DELETE FROM section WHERE section_id = ?");
				                      	pstmt.setInt(1, section_id);
				                      	pstmt.executeUpdate();
				                        }
				                  
				                   	
				                  	//delete class    
		                   			pstmt = conn.prepareStatement(
		                            "DELETE FROM class WHERE class_id = ?");
		                      		pstmt.setInt(1, class_id);
		                      		pstmt.executeUpdate();
				                 }
			                   	
			                   	//delete prerequisite
			                   	pstmt = conn.prepareStatement(
		                            "DELETE FROM prerequisite WHERE course_number = ? OR prerequisite_number = ?" );
			                   	pstmt.setString(1, request.getParameter("course_number"));
			                   	pstmt.setString(2, request.getParameter("course_number"));
		                      	pstmt.executeUpdate();
			                   	
			                  	//delete course    
	                   			pstmt = conn.prepareStatement(
	                            "DELETE FROM course WHERE course_number = ?");
	                      		pstmt.setString(1, request.getParameter("course_number"));
	                      		pstmt.executeUpdate();
			                    
                 
	                        conn.commit();
	                        conn.setAutoCommit(true);
						}
					
						// Create the statement
						Statement statement = conn.createStatement();
						
						// SELECT course numbers from course table.
						ResultSet rsCourse = statement.executeQuery("SELECT course_number " +
								"FROM course ORDER BY course_number");
						List<String> courses = new ArrayList<String>();
						while (rsCourse.next()) {
							courses.add(rsCourse.getString("course_number"));
						}

						// Use the created statement to SELECT
						// the student attributes FROM the student table.
						ResultSet rsCourses = statement.executeQuery("SELECT * FROM course");
						CachedRowSet rs = new CachedRowSetImpl();
						rs.populate(rsCourses);
				%>
				<table border="1">
					<tr>
						<th>Course Number</th>
						<th>Lab Required</th>
						<th>Letter Grade</th>	
						<th>S/U Grade</th>	
						<th>Max Units</th>
						<th>Min Units</th>
						<th>Prerequisites</th>
					</tr>
					<tr>
						<form action="course.jsp" method="POST">
							<input type="hidden" value="insert" name="action">
							<th><input value="" name="course_number" size="10"></th>
							<th>
								<select name="lab_required" style="width:100px">
									<option value="YES">YES</option>
									<option value="NO">NO</option>
								</select>
							</th>
							<th>
								<select name="letter_grade_allowed" style="width:100px">
									<option value="YES">YES</option>
									<option value="NO">NO</option>
								</select>
							</th>
							<th>
								<select name="su_allowed" style="width:100px">
									<option value="YES">YES</option>
									<option value="NO">NO</option>
								</select>
							</th>
							<th>
								<select name="max_units" style="width:100px">
                            	<% for (int i = 1; i <= 12; i++) { %>
									<option value="<%= i %>"><%= i %></option>
								<% } %>
								</select>
							</th>	
							<th>
								<select name="min_units" style="width:100px">
                            	<% for (int i = 1; i <= 12; i++) { %>
									<option value="<%= i %>"><%= i %></option>
								<% } %>
								</select>
							</th>
							<th>
								<select name="prerequisites" size="3" multiple="true" style="width:100px">
									<%
										for (String course : courses) {
									%>
											<option value="<%= course %>"><%= course %></option>
									<%
										}
									%>
								</select>
							</th>						
							<th><input type="submit" value="Insert"></th>
						</form>
					</tr>
					<%
							// Iterate over the ResultSet
							while (rs.next()) {  
					%>
					<tr>
                        <form action="course.jsp" method="POST">
                            <input type="hidden" value="update" name="action">
                            <td>
                            	<input type="hidden" value="<%= rs.getString("course_number") %>" name="course_number">
                                <%= rs.getString("course_number") %>
                            </td>
                            <td> 
                            	<% String lab_required = rs.getString("lab_required"); %>
								<select name="lab_required" style="width:100px">
									<option value="YES" <% if (lab_required.equals("YES")) out.print("selected='selected'"); %>>YES</option>
									<option value="NO" <% if (lab_required.equals("NO")) out.print("selected='selected'"); %>>NO</option>
								</select>
                            </td>           
                            <td>
                            	<% String letter_grade_allowed = rs.getString("letter_grade_allowed"); %>
                                <select name="letter_grade_allowed" style="width:100px">
									<option value="YES" <% if (letter_grade_allowed.equals("YES")) out.print("selected='selected'"); %>>YES</option>
									<option value="NO" <% if (letter_grade_allowed.equals("NO")) out.print("selected='selected'"); %>>NO</option>
								</select>
                            </td>
                            <td>
                             <% String su_allowed = rs.getString("su_allowed"); %>
                                <select name="su_allowed" style="width:100px">
									<option value="YES" <% if (su_allowed.equals("YES")) out.print("selected='selected'");%>>YES</option>
									<option value="NO" <% if (su_allowed.equals("NO")) out.print("selected='selected'");%>>NO</option>
								</select>
                            </td>
                            <td>
                            <% int max_units = rs.getInt("max_units"); %>
                            	<select name="max_units" style="width:100px">
                            	<% for (int i = 1; i <= 12; i++) { %>
									<option value="<%= i %>" <% if (max_units == i) out.print("selected='selected'"); %>><%= i %></option>
								<% } %>
								</select>
                            </td>
                            <td>
                             	<% int min_units = rs.getInt("min_units"); %>
                            	<select name="min_units" style="width:100px">
                            	<% for (int i = 1; i <= 12; i++) { %>
									<option value="<%= i %>" <% if (min_units == i) out.print("selected='selected'"); %>><%= i %></option>
								<% } %>
								</select>
                            </td>
                            <td>
                            	<%
                            		PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM prerequisite WHERE course_number = ?");
                        			pstmt.setString(1, rs.getString("course_number"));	
                        			ResultSet rsPrerequisite = pstmt.executeQuery();
                        			Set<String> prerequisites = new HashSet<String>();
                        			while (rsPrerequisite.next()) {
                        				prerequisites.add(rsPrerequisite.getString("prerequisite_number"));
                        			}
                            	%>
                            	<select name="prerequisites" size="3" multiple="true" style="width:100px">
								 <%
								 	String current = rs.getString("course_number");
									for (String course : courses) {
										if (!course.equals(current)) {
								 %>
									<option value="<%= course %>" 
								 			<% if (prerequisites.contains(course)) out.print("selected='true'"); %>>
								 		<%= course %>
								 	</option>
								 <%
								 		}
								 	}
								 %>
								</select>
                            </td>
                            <td>
                                <input type="submit" value="Update">
                            </td>
                        </form>
                        
                        <form action="course.jsp" method="POST">
                            <input type="hidden" value="delete" name="action">
                            <input type="hidden" 
                                value="<%= rs.getString("course_number") %>" name="course_number">
                            <td>
                                <input type="submit" value="Delete">
                            </td>
                        </form>
                    </tr>
					<%
							}
					%>
					<%
						} catch (SQLException e) {
							System.out.println(e.getMessage());
							conn.rollback();
						} catch (Exception e) {
							System.out.println(e.getMessage());
							conn.rollback();
						}
					%>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>



